package com.dudu.dao;

import java.sql.Connection;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import com.dudu.model.Signcode;

import net.sf.json.JSONObject;
import core.CoreDao;
import core.db.DBConnectionUtil;

public class SmsConfigureDao {
	/**
	 * 根据app_key查询已配置的签名
	 * @param dao
	 * @param app_key
	 * @return
	 */
	public List<JSONObject> querySignCodeApp(CoreDao dao,String app_key){
		Connection con = null;
		List<JSONObject> result = null;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer(
					" select * FROM t_sms_signcode_app where appkey=?");
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(),
					new Object[] { app_key });
			if (list != null && list.size() > 0) {
				result = list;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	/**
	 * 新增app_key与签 名的关系
	 * @param dao
	 * @param app_key
	 * @param signcode
	 * @return
	 */
	
	public int insertSignCodeApp(CoreDao dao,String app_key,String signcode){
		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer(
					"INSERT INTO t_sms_signcode_app(appkey, signcode)");
			sql.append("VALUES(?, ?)");

			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] { 
						app_key, signcode
					});

			JSONObject obj = list.get(0);
			result = obj.optInt("result");

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	/**
	 * 根据app_key删除签名
	 * @param dao
	 * @param app_key
	 * @return
	 */
	public int deleteSignCodeByAppKey(CoreDao dao, String app_key) {

		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			
			StringBuffer sql = new StringBuffer(" delete from t_sms_signcode_app where appkey =? ");
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(),	new Object[] { app_key });

			if (list != null && list.size() > 0) {
				list.get(0);
				JSONObject obj = list.get(0);
				result = obj.optInt("result");
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	/**
	 * 新增模板与app_key的关系
	 * @param dao
	 * @param app_key
	 * @param template_code
	 * @return
	 */
	public int insertTemplateApp(CoreDao dao,String app_key,String template_code){
		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer(
					"INSERT INTO t_sms_template_app(appkey, template_code)");
			sql.append("VALUES(?, ?)");

			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] { 
						app_key, template_code
					});

			JSONObject obj = list.get(0);
			result = obj.optInt("result");

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	/**
	 * 根据app_key删除所有模板的关系
	 * @param dao
	 * @param app_key
	 * @return
	 */
	public int deleteTemplateByAppKey(CoreDao dao, String app_key) {

		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			
			StringBuffer sql = new StringBuffer(" delete from t_sms_template_app where appkey =? ");
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(),
					new Object[] { app_key });

			if (list != null && list.size() > 0) {
				list.get(0);
				JSONObject obj = list.get(0);
				result = obj.optInt("result");
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	/**
	 * 删除app_key与模板的关系
	 * @param dao
	 * @param app_key
	 * @param template_code
	 * @return
	 */
	public int deleteTemplateByAppKey(CoreDao dao, String app_key,String template_code) {

		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			
			StringBuffer sql = new StringBuffer(" delete from t_sms_template_app where appkey =? and template_code=?");
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(),
					new Object[] { app_key,template_code });

			if (list != null && list.size() > 0) {
				list.get(0);
				JSONObject obj = list.get(0);
				result = obj.optInt("result");
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	/**
	 * 未配置的模板总数
	 * 查询条数（分页用）
	 * @param paramSql
	 * @return
	 */
	public int getTotalTemplateApp(CoreDao dao,String app_key, String paramSql) {
		if(StringUtils.isNotBlank(paramSql)){
			paramSql = " and " + paramSql;
		}
		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer(
					" select count(1) as totalAcount from t_sms_template where template_code not in(select template_code from t_sms_template_app where appkey='"+app_key+"') "
					+ paramSql );
				
			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] { });

			JSONObject obj = list.get(0);
			result = (Integer) obj.get("totalAcount");

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	/**
	 * 未配置的模板列表
	 * @param dao
	 * @param app_key
	 * @param paramSql
	 * @param pageSql
	 * @return
	 */
	public List<JSONObject> queryAllUnTemplateApp(CoreDao dao,String app_key,String paramSql,String pageSql){
		Connection con = null;
		List<JSONObject> result = null;
		if(StringUtils.isNotBlank(paramSql)){
			paramSql = " and " + paramSql;
		}

		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer(
					"select * from t_sms_template where template_code not in(select template_code from t_sms_template_app where appkey='"+app_key+"') "
					+ paramSql
					+ " order by create_time desc "
					+ pageSql );
			
			result = dao.serviceJsonSql(con, sql.toString(),
					new Object[] { null });

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	/**
	 * 已配置的模板列表
	 * @param dao
	 * @param app_key
	 * @return
	 */
	public List<JSONObject> queryAllTemplateApp(CoreDao dao,String app_key) {
		Connection con = null;
		List<JSONObject> result = null;

		try {
			con = DBConnectionUtil.getDBConnection("emate1");

			StringBuffer sql = new StringBuffer("select temp.template_code,temp.template_name from t_sms_template_app app,t_sms_template temp where app.template_code=temp.template_code and app.appkey=? ");
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(),	new Object[] {app_key  });
			result=list;
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
}
